<?php
/**
 * Created by PhpStorm.
 * User: seven
 * Date: 2018/3/4
 * Time: 下午10:29
 */

class PDOManager{

    static function getDB(){
        $db = new PDO("mysql:host=rm-bp16ad3ce8188drq6o.mysql.rds.aliyuncs.com;dbname=webee", "root", "cowthan_777");
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $db;
    }

    /**
     * @param $db
     * @param $sql
     * @param $assoc_params
     * @param $fields 指定感兴趣的列，null或空则表示对所有列感兴趣
     * @return array  返回数组
     */
    static function findAll($db, $sql, $assoc_params, $fields = null){
//    $sql = "select * from contact where real_name LIKE :keyword";
        $statement = $db->prepare($sql);

        foreach ($assoc_params as $key => $value) {
//            echo $key ."--" . gettype ($value) . "--<br/>" ;

            if(gettype ($value) === "string"){
                $statement->bindParam(':' . $key, $value, PDO::PARAM_STR);
            }else if(gettype ($value) === "integer"){
                $statement->bindParam(':' . $key, $value, PDO::PARAM_INT);
            }else{
                $statement->bindParam(':' . $key, $value);
            }
        }
        $statement->execute();

        $res = array();
        while($row = $statement->fetch(PDO::FETCH_ASSOC)){
            $res[] = PDOManager::extractFields($row, $fields);
        }
        return $res;
    }


    /**
     * @param $db
     * @param $sql
     * @param $assoc_params
     * @param $fileds 指定感兴趣的列，null或空则表示对所有列感兴趣
     * @return array
     */
    static function findOne($db, $sql, $assoc_params, $fileds = null){
//    $sql = "select * from contact where real_name LIKE :keyword";
        $statement = $db->prepare($sql);

        foreach ($assoc_params as $key => $value) {
            $statement->bindParam(':' . $key, $value);
        }
        $statement->execute();


        $res = array();
        while($row = $statement->fetch(PDO::FETCH_ASSOC)){
            $res[] = PDOManager::extractFields($row, $fileds);
        }
        return $res;

        $row = $statement->fetch(PDO::FETCH_ASSOC);
        if($row){
            return PDOManager::extractFields($row, $fileds);
        }else{
            return null;
        }
    }

    /**
     * @param $db
     * @param $sql
     * @param $assoc_params
     * @param $columnName  数据库的一个字段名，以此为值
     * @param null $columnAsKey  数据库的一个字段名，如果字段名存在且有值，则对应的列值就是返回的数组的key，否则就是下标数组
     * @return array
     */
    static function findColumns($db, $sql, $assoc_params, $columnName, $columnAsKey = null){
//    $sql = "select * from contact where real_name LIKE :keyword";
        $statement = $db->prepare($sql);

        foreach ($assoc_params as $key => $value) {
            $statement->bindParam(':' . $key, $value);
        }
        $statement->execute();

        $res = array();
        while($row = $statement->fetch(PDO::FETCH_ASSOC)){
            $value = null;
            if(array_key_exists($columnName, $row)){
                $value = $row[$columnName];
            }

            if($columnAsKey && array_key_exists($columnAsKey, $row) && $row[$columnAsKey]){
                $res[$row[$columnAsKey]] = $value;
            }else{
                $res[] = $value;
            }

        }
        return $res;
    }

    /**
     * @param $db
     * @param $sql
     * @param $assoc_params
     * @param $columnName 数据库的一个字段名，以此为值
     * @return null
     */
    static function findOneColumn($db, $sql, $assoc_params, $columnName){
//    $sql = "select * from contact where real_name LIKE :keyword";
        $statement = $db->prepare($sql);

        foreach ($assoc_params as $key => $value) {
            $statement->bindParam(':' . $key, $value);
        }
        $statement->execute();

        $row = $statement->fetch(PDO::FETCH_ASSOC);
        if($row){
            if(array_key_exists($columnName, $row)){
                return $row[$columnName];
            }else{
                return null;
            }
        }else{
            return null;
        }
    }



    private static function extractFields($rawArray, $fields){
        if($fields == null || count($fields) == 0) return $rawArray;

        $res = array();
        foreach ($fields as $key => $value){
            if(array_key_exists($key, $rawArray)){
                $res[$key] = $rawArray[$key];
            }else{
                $res[$key] = null;
            }
        }
        return $res;
    }
}